-- FlowPlan性能优化脚本
-- 说明: 提供数据库性能优化相关的配置和脚本

-- 1. 分区表策略
-- 为审计日志表按月分区
CREATE TABLE audit_log_template (
    id BIGSERIAL,
    operator_id BIGINT NOT NULL,
    operation_type VARCHAR(30) NOT NULL,
    resource_type VARCHAR(30) NOT NULL,
    resource_id BIGINT,
    old_value JSONB,
    new_value JSONB,
    ip_address INET,
    user_agent TEXT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (create_time);

-- 创建分区示例
CREATE TABLE audit_log_202403 PARTITION OF audit_log_template
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

CREATE TABLE audit_log_202404 PARTITION OF audit_log_template
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- 2. 物化视图优化
-- 创建物化视图用于频繁查询的项目统计
CREATE MATERIALIZED VIEW mv_project_stats AS
SELECT 
    p.id as project_id,
    p.project_name,
    p.status,
    COUNT(t.id) as total_todos,
    COUNT(CASE WHEN t.status = 'DONE' THEN 1 END) as completed_todos,
    ROUND(
        CASE 
            WHEN COUNT(t.id) > 0 
            THEN (COUNT(CASE WHEN t.status = 'DONE' THEN 1 END) * 100.0 / COUNT(t.id))
            ELSE 0 
        END, 2
    ) as completion_percentage,
    MAX(t.update_time) as last_activity_time
FROM project p
LEFT JOIN todo t ON p.id = t.project_id
GROUP BY p.id, p.project_name, p.status
WITH NO DATA;

-- 创建索引以优化物化视图查询
CREATE INDEX idx_mv_project_stats_status ON mv_project_stats(status);
CREATE INDEX idx_mv_project_stats_completion ON mv_project_stats(completion_percentage);

-- 刷新物化视图的存储过程
CREATE OR REPLACE PROCEDURE refresh_project_stats_materialized_view()
LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_project_stats;
    RAISE NOTICE '项目统计物化视图刷新完成';
END;
$$;

-- 3. 查询优化建议
-- 为复杂查询创建复合索引
CREATE INDEX idx_todo_complex_query ON todo(project_id, status, assignee_id, due_date)
WHERE status IN ('TODO', 'IN_PROGRESS');

CREATE INDEX idx_project_search ON project USING gin(
    to_tsvector('chinese', project_name || ' ' || COALESCE(description, ''))
);

-- 4. 数据库配置优化建议
-- 以下配置建议添加到postgresql.conf中
/*
# 内存配置
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# 检查点配置
checkpoint_completion_target = 0.9
wal_buffers = 16MB
min_wal_size = 100MB
max_wal_size = 2GB

# 查询优化
random_page_cost = 1.1
effective_io_concurrency = 200

# 连接配置
max_connections = 100
superuser_reserved_connections = 3
*/

-- 5. 定期维护任务
-- 创建定期清理和分析的脚本
CREATE OR REPLACE PROCEDURE perform_database_maintenance()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 分析表统计信息
    ANALYZE;
    
    -- 清理过期的审计日志（保留最近6个月）
    DELETE FROM audit_log WHERE create_time < NOW() - INTERVAL '6 months';
    
    -- 刷新物化视图
    CALL refresh_project_stats_materialized_view();
    
    -- 重建索引（如果需要）
    -- REINDEX TABLE project;
    -- REINDEX TABLE todo;
    
    RAISE NOTICE '数据库维护任务完成';
END;
$$;

-- 6. 性能监控视图
-- 创建查询性能监控视图
CREATE OR REPLACE VIEW v_database_performance AS
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 7. 慢查询监控
-- 启用慢查询日志的配置建议
/*
# 在postgresql.conf中添加以下配置
log_min_duration_statement = 1000  # 记录执行时间超过1秒的查询
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
*/